﻿/*    ==脚本参数==

    源服务器版本 : SQL Server 2016 (13.0.1601)
    源数据库引擎版本 : Microsoft SQL Server Enterprise Edition
    源数据库引擎类型 : 独立的 SQL Server

    目标服务器版本 : SQL Server 2016
    目标数据库引擎版本 : Microsoft SQL Server Enterprise Edition
    目标数据库引擎类型 : 独立的 SQL Server
*/

USE [Live]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author:		<chenkuan>
-- Create date: <2017/11/09>
-- Description:	<物料申领>
-- =============================================
CREATE PROCEDURE [dbo].[Pro_JobMtlApp] 
	@AppMtlQty decimal(8,2),
	@AppMtlUser nvarchar(50),
	@AppTime nvarchar(50),
	@GetMtlUser nvarchar(50),
	@SysRowId nvarchar(50),
	@AppNum int,
	@Memo nvarchar(500)=null
AS

BEGIN
	DECLARE @JobNum nvarchar(50)
	DECLARE @PartNum nvarchar(50)
	DECLARE @PartDesc nvarchar(500)
	DECLARE @AsmSeq int
	DECLARE @MtlSeq int

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

	UPDATE JobMtl SET AppMtlNum_c=@AppNum,AppMtlQty_c +=@AppMtlQty,
	AppMtlUserID_c=@AppMtlUser,AppMtlTime_c=@AppTime,GetMtlUserID_c=@GetMtlUser
	WHERE  SysRowID = @SysRowId

	SELECT @JobNum=JobNum,@PartNum=PartNum,@PartDesc=[Description],@AsmSeq=AssemblySeq,@MtlSeq=MtlSeq FROM JobMtl where SysRowID=@SysRowId

	INSERT dbo.MtlAppHistory (Company,JobNum,AppMtlNum,PartNum,PartDesc,AssmbleSeq,
	MtlSeq,AppMtlQty,AppMtlTime,AppMtlUserID,GetMtlUser,AppType,ForSysRowID,CommentText) values 
	('001',@JobNum,@AppNum,@PartNum,@PartDesc,@AsmSeq,@MtlSeq,@AppMtlQty,@AppTime,@AppMtlUser,@GetMtlUser,'APP',@SysRowId,@Memo)

	COMMIT TRANSACTION
END
GO

-- =============================================
-- Author:		<chenkuan>
-- Create date: <2018/06/30>
-- Description:	<工单物料撤销申领>
-- =============================================
CREATE PROCEDURE [dbo].[Pro_JobMtlCancelApp] 
	@JobNum nvarchar(50),
	@AssmbleSeq int,
	@MtlSeq int,
	@AppMtlNum int,
	@AppQty decimal(8,2)	
AS

BEGIN

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

	UPDATE dbo.JobMtl SET AppMtlQty_c -= @AppQty WHERE JobNum = @JobNum and AssemblySeq=@AssmbleSeq and MtlSeq=@MtlSeq

	DELETE FROM dbo.MtlAppHistory WHERE JobNum =@JobNum and AssmbleSeq=@AssmbleSeq and MtlSeq=@MtlSeq and AppMtlNum=@AppMtlNum and AppType='APP'

	COMMIT TRANSACTION
END
GO

-- =============================================
-- Author:		<chenkuan>
-- Create date: <2018/06/30>
-- Description:	<物料撤销备料>
-- =============================================
CREATE PROCEDURE [dbo].[Pro_JobMtlCancelPrep] 
	@JobNum nvarchar(50),
	@AssmbleSeq int,
	@MtlSeq int,
	@AppMtlNum int,
	@PrepQty decimal(8,2),
	@WhseCode nvarchar(50),
	@BinNum nvarchar(50),
	@LotNum nvarchar(50)	
AS

BEGIN

	DECLARE @PartNum nvarchar(50)
	SELECT @PartNum=PartNum from dbo.MtlAppHistory WHERE JobNum=@JobNum and AssmbleSeq=@AssmbleSeq and MtlSeq=@MtlSeq and AppType='APP'

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

	UPDATE dbo.JobMtl SET PrepMtlNeedQty_c -=@PrepQty WHERE JobNum=@JobNum and AssemblySeq=@AssmbleSeq and MtlSeq=@MtlSeq

	DELETE FROM dbo.MtlAppHistory 
	WHERE AppType='PREP' and JobNum = @JobNum and AssmbleSeq=@AssmbleSeq and MtlSeq=@MtlSeq and AppMtlNum=@AppMtlNum

	UPDATE dbo.MtlAppHistory SET PrepMtlQty -=@PrepQty,PrepMtlTime=null,PrepMtlUserID='' 
	WHERE AppType='APP' and JobNum = @JobNum and AssmbleSeq=@AssmbleSeq and MtlSeq=@MtlSeq and AppMtlNum=@AppMtlNum

	UPDATE dbo.PartBin SET PrepQty_c -= @PrepQty 
	WHERE WarehouseCode = @WhseCode and BinNum=@BinNum and LotNum=@LotNum and PartNum=@PartNum

	UPDATE PartBin set PrepQty_c=0 where PrepQty_c<0
	UPDATE JobMtl set PrepMtlNeedQty_c=0 where PrepMtlNeedQty_c<0 

	COMMIT TRANSACTION
END
GO

-- =============================================
-- Author:		<chenkuan>
-- Create date: <2019-06-17>
-- Description:	<查询物料库存可用量，指定仓库代码>
-- =============================================
CREATE FUNCTION [dbo].[GetNowOnhandWhse]
(
	@BuyIt bit,
	@PartNum varchar(50),
	@JobNum varchar(50),
	@AsmblSeq int,
	@MtlSeq int,
	@AllowWhse nvarchar(500)
)
RETURNS  decimal(18,2)
AS
BEGIN
	declare @OnhandQty decimal(18,2)=0

	if (@BuyIt=0)
		begin
			select @OnhandQty=ISNULL(sum(OnhandQty-AllocatedQty-PrepQty_c),0) from PartBin 
			where PartNum=@PartNum and (LotNum='00' or JobNum_c=@JobNum)
			and CHARINDEX(WarehouseCode,@AllowWhse,0)>0
			group by PartNum

		end
	else
		begin
			select @OnhandQty=ISNULL(sum(OnhandQty-AllocatedQty-PrepQty_c),0) from PartBin 
			where PartNum=@PartNum and ((JobNum_c=@JobNum and AssemblySeq_c=@AsmblSeq and MtlSeq_c=@MtlSeq) or LotNum='00')
			and CHARINDEX(WarehouseCode,@AllowWhse,0)>0
			group by PartNum

		end

		if(@OnhandQty<0)
			set @OnhandQty=0

	RETURN @OnhandQty
END
GO

-- =============================================
-- Author:		<chenkuan>
-- Create date: <2018-06-28>
-- Description:	<查询物料库存可用量>
-- =============================================
CREATE FUNCTION [dbo].[GetNowOnhand]
(
	@BuyIt bit,
	@PartNum varchar(50),
	@JobNum varchar(50),
	@AsmblSeq int,
	@MtlSeq int
)
RETURNS  decimal(18,2)
AS
BEGIN
	declare @OnhandQty decimal(18,2)=0
	declare @AllowWhse nvarchar(500)
	declare @IsNd bit=0

	select  @IsNd=(case when t.CustID='NDATL' or t.CustID='NDCATL' then 1 else 0 end)  from (
	select a.JobNum,b.CustNum,a.OrderNum,a.OrderLine,a.OrderRelNum,c.CustID,c.Name from Live.Erp.JobProd a
	inner join Live.Erp.OrderHed b on b.Company=a.Company and b.OrderNum=a.OrderNum
	left join Live.Erp.Customer c on c.Company=b.Company and c.CustNum=b.CustNum
	) t where t.JobNum=@JobNum

	if(@IsNd=0)
		set @AllowWhse='WGJ,XHP,YCL,XCK,CPK,WXP,BCP'
	else
		set @AllowWhse='NDCK'

	if (@BuyIt=0)
		begin
			select @OnhandQty=ISNULL(sum(OnhandQty-AllocatedQty-PrepQty_c),0) from PartBin 
			where PartNum=@PartNum and (LotNum='00' or JobNum_c=@JobNum)
			and CHARINDEX(WarehouseCode,@AllowWhse,0)>0
			group by PartNum

		end
	else
		begin
			select @OnhandQty=ISNULL(sum(OnhandQty-AllocatedQty-PrepQty_c),0) from PartBin 
			where PartNum=@PartNum and ((JobNum_c=@JobNum and AssemblySeq_c=@AsmblSeq and MtlSeq_c=@MtlSeq) or LotNum='00')
			and CHARINDEX(WarehouseCode,@AllowWhse,0)>0
			group by PartNum

		end

		if(@OnhandQty<0)
			set @OnhandQty=0

	RETURN @OnhandQty
END
GO

-- =============================================
-- Author:		<chenkuan>
-- Create date: <2018/10/09>
-- Description:	<物料发料后续处理>
-- =============================================
CREATE PROCEDURE [dbo].[Pro_JobMtlIssue] 
	@IssueQty decimal(8,2),
	@WhseCode nvarchar(50),
	@BinNum nvarchar(50),
	@LotNum nvarchar(50),
	@forSysRowId nvarchar(50),
	@sysRowId nvarchar(50)
AS

BEGIN

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

	declare @PartNum nvarchar(50)
	select top 1 @PartNum=PartNum from dbo.MtlAppHistory where SysRowID=@sysRowId

	UPDATE dbo.MtlAppHistory SET PrepComplete=1 WHERE SysRowID=@sysRowId
	UPDATE JobMtl SET PrepMtlNeedQty_c -=@IssueQty WHERE SysRowID = @forSysRowId
	UPDATE dbo.PartBin SET PrepQty_c -= @IssueQty WHERE WarehouseCode = @WhseCode and BinNum=@BinNum and LotNum=@LotNum and PrepQty_c >= @IssueQty and PartNum=@PartNum
	

	COMMIT TRANSACTION
END
GO

-- =============================================
-- Author:		<chenkuan>
-- Create date: <2018/06/30>
-- Description:	<工单物料备料>
-- =============================================
CREATE PROCEDURE [dbo].[Pro_JobMtlPrep] 
	@JobNum nvarchar(50),
	@AssmbleSeq int,
	@MtlSeq int,
	@AppMtlNum int,
	@PrepMtlUser nvarchar(50),
	@PrepQty decimal(8,2),
	@WhseCode nvarchar(50),
	@BinNum nvarchar(50),
	@LotNum nvarchar(50)	
AS

BEGIN
	DECLARE @AppQty decimal(8,2)=0
	DECLARE @PrepedQty decimal(8,2)=0
	DECLARE @PartNum nvarchar(50)

	SELECT @AppQty=AppMtlQty,@PrepedQty=PrepMtlQty,@PartNum=PartNum from dbo.MtlAppHistory WHERE JobNum=@JobNum and AssmbleSeq=@AssmbleSeq and MtlSeq=@MtlSeq and AppType='APP' and AppMtlQty<>PrepMtlQty

		BEGIN
			SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
			BEGIN TRANSACTION

			UPDATE dbo.JobMtl SET PrepMtlNeedQty_c += @PrepQty 
			WHERE JobNum = @JobNum and AssemblySeq=@AssmbleSeq and MtlSeq=@MtlSeq

			UPDATE dbo.MtlAppHistory SET PrepMtlQty += @PrepQty,PrepMtlTime=GETDATE(),PrepMtlUserID=@PrepMtlUser
			WHERE JobNum=@JobNum and AssmbleSeq=@AssmbleSeq and MtlSeq=@MtlSeq and AppType='APP' and AppMtlNum=@AppMtlNum

			INSERT INTO dbo.MtlAppHistory SELECT Company,JobNum,AppMtlNum,PartNum,PartDesc,AssmbleSeq,MtlSeq,AppMtlQty,
			@PrepQty as PrepMtlQty,AppMtlTime,GETDATE() as PrepMtlTime,AppMtlUserID,
			@PrepMtlUser as PrepMtlUserID,GetMtlUser,'PREP' as AppType,CommentText,@WhseCode as WhseCode,@BinNum as BinNum,@LotNum as LotNum,0 as PrepComplete,GETDATE() as SysTime,ForSysRowID,
			(CONVERT([uniqueidentifier],CONVERT([binary](10),newid())+CONVERT([binary](6),getutcdate()))) as SysRowID
			FROM dbo.MtlAppHistory 
			WHERE JobNum=@JobNum and AssmbleSeq=@AssmbleSeq and MtlSeq=@MtlSeq and AppType='APP' and AppMtlNum=@AppMtlNum

			UPDATE dbo.PartBin SET PrepQty_c += @PrepQty 
			WHERE WarehouseCode = @WhseCode and BinNum=@BinNum and LotNum=@LotNum and PartNum=@PartNum


			--纠正PartBin的备料占用量
			update 
				PartBin
			set
				PrepQty_c=MtlApp.SumPrepMtlQty
			from
				PartBin,
				(select PartNum,Sum(PrepMtlQty) as SumPrepMtlQty,WhseCode,BinNum,LotNum
				 from 
				MtlAppHistory where AppType='PREP' and PrepComplete=0 and WhseCode is not null and BinNum is not null and LotNum is not null
				group by PartNum,WhseCode,WhseCode,BinNum,LotNum
				) MtlApp
			where
				MtlApp.PartNum = PartBin.PartNum and MtlApp.WhseCode = PartBin.WarehouseCode and MtlApp.BinNum = PartBin.BinNum and MtlApp.LotNum = PartBin.LotNum

			--纠正JobMtl的备料占用量
			update 
				JobMtl
			set
				PrepMtlNeedQty_c=MtlApp.SumPrepMtlQty
			from
				JobMtl,
				(select PartNum,Sum(PrepMtlQty) as SumPrepMtlQty,JobNum,AssmbleSeq,MtlSeq
				 from 
				MtlAppHistory where AppType='PREP' and PrepComplete=0 and WhseCode is not null and BinNum is not null and LotNum is not null
				group by PartNum,WhseCode,JobNum,AssmbleSeq,MtlSeq
				) MtlApp
			where
				MtlApp.PartNum = JobMtl.PartNum and MtlApp.JobNum = JobMtl.JobNum and MtlApp.AssmbleSeq = JobMtl.AssemblySeq and MtlApp.MtlSeq = JobMtl.MtlSeq

			COMMIT TRANSACTION
		END
END
GO

-- =============================================
-- Author:		<chenkuan>
-- Create date: <2018/06/30>
-- Description:	<工单物料退料>
-- =============================================
CREATE PROCEDURE [dbo].[Pro_JobMtlReturn] 
	@IssueQty decimal(8,2),
	@WhseCode nvarchar(50),
	@BinNum nvarchar(50),
	@LotNum nvarchar(50),
	@forSysRowId nvarchar(50),
	@sysRowId nvarchar(50)
AS

BEGIN
	DECLARE @PartNum nvarchar(50)

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

	select @PartNum=PartNum from JobMtl where SysRowID=@forSysRowId
	UPDATE JobMtl SET PrepMtlNeedQty_c +=@IssueQty WHERE SysRowID = @forSysRowId
	UPDATE dbo.PartBin SET PrepQty_c += @IssueQty WHERE WarehouseCode = @WhseCode and BinNum=@BinNum and LotNum=@LotNum and PartNum=@PartNum
	UPDATE dbo.MtlAppHistory SET PrepComplete=0 WHERE SysRowID=@sysRowId

	COMMIT TRANSACTION
END
GO

-- =============================================
-- Author:		<chenkuan>
-- Description:	<工单物料申领用表>
-- =============================================
CREATE TABLE [dbo].[MtlAppHistory](
	[Company] [nvarchar](50) NULL,
	[JobNum] [nvarchar](50) NULL,
	[AppMtlNum] [int] NULL,
	[PartNum] [nvarchar](50) NULL,
	[PartDesc] [nvarchar](max) NULL,
	[AssmbleSeq] [int] NULL,
	[MtlSeq] [int] NULL,
	[AppMtlQty] [decimal](18, 2) NOT NULL,
	[PrepMtlQty] [decimal](18, 2) NOT NULL,
	[AppMtlTime] [datetime] NULL,
	[PrepMtlTime] [datetime] NULL,
	[AppMtlUserID] [nvarchar](50) NULL,
	[PrepMtlUserID] [nvarchar](50) NULL,
	[GetMtlUser] [nvarchar](50) NULL,
	[AppType] [nvarchar](10) NULL,
	[CommentText] [nvarchar](500) NULL,
	[WhseCode] [nvarchar](50) NULL,
	[BinNum] [nvarchar](50) NULL,
	[LotNum] [nvarchar](50) NULL,
	[PrepComplete] [bit] NOT NULL,
	[SysTime] [datetime] NOT NULL,
	[ForSysRowID] [nvarchar](50) NULL,
	[SysRowID] [uniqueidentifier] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MtlAppHistory] ADD  CONSTRAINT [DF_MtlAppHistory_AppMtlQty]  DEFAULT ((0)) FOR [AppMtlQty]
GO

ALTER TABLE [dbo].[MtlAppHistory] ADD  CONSTRAINT [DF_MtlAppHistory_PrepMtlQty]  DEFAULT ((0)) FOR [PrepMtlQty]
GO

ALTER TABLE [dbo].[MtlAppHistory] ADD  CONSTRAINT [DF_MtlAppHistory_PrepComplete]  DEFAULT ((0)) FOR [PrepComplete]
GO

ALTER TABLE [dbo].[MtlAppHistory] ADD  CONSTRAINT [DF_MtlAppHistory_SysTime]  DEFAULT (getdate()) FOR [SysTime]
GO

ALTER TABLE [dbo].[MtlAppHistory] ADD  CONSTRAINT [DF_MtlAppHistory_SysRowID]  DEFAULT (CONVERT([uniqueidentifier],CONVERT([binary](10),newid())+CONVERT([binary](6),getutcdate()))) FOR [SysRowID]
GO
